USE [Exp]
GO
/****** Object:  View [dbo].[vw_BLSummary]    Script Date: 11/30/2022 18:01:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*----------------------------------------------------------------*/
ALTER VIEW [dbo].[vw_BLSummary]
AS
SELECT DISTINCT 
                      dbo.Invoice_Bank.Inv_BankID, dbo.Invoice_Bank.Inv_BankVNo, dbo.Invoice_Bank.BLNo, dbo.Invoice_Bank.BLDate, dbo.Invoice_Bank.GD, dbo.Invoice_Bank.BankID, 
                      dbo.Invoice_Bank.ConsigneeID, dbo.Accounts.AccountTitle AS ConsigneeName, dbo.Invoice_Bank.ContainerTypeID, ISNULL(SSPInvoice.BENo, 0) AS SSPBL, ISNULL(AgentInvoice.BENo, 0) 
                      AS AgentBL, ISNULL(TransInvoice.BENo, 0) AS TransBL, ISNULL(FreightInvoice.BENo, 0) AS FreightBL
FROM         dbo.Invoice_Bank LEFT OUTER JOIN
                      dbo.Accounts ON dbo.Invoice_Bank.ConsigneeID = dbo.Accounts.AccountNo LEFT OUTER JOIN
                          (SELECT DISTINCT 
                                                   dbo.Invoice_Freight.BENo, dbo.Invoice_Freight.ContainerNo, dbo.Invoice_FreightDetail.AgentID, dbo.Invoice_FreightDetail.NetAmount, dbo.Invoice_FreightDetail.SaleDate
                            FROM          dbo.Invoice_FreightDetail RIGHT OUTER JOIN
                                                   dbo.Invoice_Freight ON dbo.Invoice_FreightDetail.inv_FreightID = dbo.Invoice_Freight.Inv_FreightID) AS FreightInvoice ON 
                      dbo.Invoice_Bank.BLNo = FreightInvoice.BENo LEFT OUTER JOIN
                          (SELECT DISTINCT dbo.Invoice_Trans.BENo, dbo.Invoice_Trans.ContainerNo, dbo.Invoice_TransDetail.TransID, dbo.Invoice_TransDetail.NetFreight, dbo.Invoice_TransDetail.SaleDate
                            FROM          dbo.Invoice_Trans LEFT OUTER JOIN
                                                   dbo.Invoice_TransDetail ON dbo.Invoice_Trans.Inv_TransID = dbo.Invoice_TransDetail.inv_TransID) AS TransInvoice ON 
                      dbo.Invoice_Bank.BLNo = TransInvoice.BENo LEFT OUTER JOIN
                          (SELECT DISTINCT dbo.Invoice_Agent.BENo, dbo.Invoice_Agent.ContainerNo, dbo.Invoice_AgentDetail.AgentID, dbo.Invoice_AgentDetail.SaleDate
                            FROM          dbo.Invoice_Agent LEFT OUTER JOIN
                                                   dbo.Invoice_AgentDetail ON dbo.Invoice_Agent.Inv_AgentID = dbo.Invoice_AgentDetail.inv_AgentID) AS AgentInvoice ON dbo.Invoice_Bank.BLNo = AgentInvoice.BENo LEFT OUTER JOIN
                          (SELECT DISTINCT dbo.Invoice_SSP.BENo, dbo.Invoice_SSParty.ContainerNo, dbo.Invoice_SSParty.ItemID, dbo.Invoice_SSParty.VarietyID
                            FROM          dbo.Invoice_SSP INNER JOIN
                                                   dbo.Invoice_SSParty ON dbo.Invoice_SSP.Inv_SSPID = dbo.Invoice_SSParty.inv_SSPID
                            WHERE      (dbo.Invoice_SSParty.ContainerNo IS NOT NULL)) AS SSPInvoice ON dbo.Invoice_Bank.BLNo = SSPInvoice.BENo
WHERE     (0 = 0)